﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;

public partial class A_ImportPage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //if (!IsPostBack)
        //{
            DL1.Text = "请选择导入数据";
            DL2.Text = "请选择导出数据";
            DL3.Text = "请选择删除数据";

        //}
    }
    protected void btnExport_Click(object sender, EventArgs e)
    {
        string filepath = this.fileUpload.PostedFile.FileName;
        ExcelToSQL ets = new ExcelToSQL();
        /*该部分获取的是文件名，没有路径，所以很有可能找不到该excel文件，所以如果有问题便可以这样写：   
        string fileName = fileUpload.PostedFile.FileName;          
         fileUpload.PostedFile.SaveAs(Server.MapPath("upfile") + "\\" + fileName);          
        string filepath = Server.MapPath("upfile") + "\\" + fileName; */
        if (filepath != "")
        {
            if (this.DL1.Text != "请选择导入数据表")
            {
                string tableName = "";
                if (DL1.Text == "学生基本信息")
                    tableName = "STU_INFO";
                else if (DL1.Text == "教师基本信息")
                    tableName = "TEA_INFO";
                else tableName = "COURSE_INFO";

                if (filepath.Contains("xls"))
                {
                    int result = ets.ImportSql(filepath, tableName); //this.txtTableName.Text获得数据库表名，如"student"
                    if (result == (int)ExcelToSQL.ImportState.tableNameError)
                        ets.Alert("此表名在数据中不存在！");
                    else if (result == (int)ExcelToSQL.ImportState.excelFormatError)
                        ets.Alert("Excel格式不能正确读取！");
                    else if (result == (int)ExcelToSQL.ImportState.fieldMatchError)
                        ets.Alert("Excel里的字段和Sql Server里的字段不匹配！");
                    else if (result == (int)ExcelToSQL.ImportState.dataTypeError)
                        ets.Alert("转换数据类型时发生错误!");
                    else if (result == (int)ExcelToSQL.ImportState.right)
                    {
                        ets.Alert("导入成功");
                    }
                }
                else ets.Alert("上传的文件类型必须为excel文件！");
           }
            else ets.Alert("请选择导入数据表！");
        }
        else ets.Alert("没有选择要上传的文件！");
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        DataTableToExcel dt = new DataTableToExcel();
        //string tableName = "";
        string str_sql = "";
        if (DL2.Text != "请选择导出数据表")
        {
            //if (DL2.Text == "学生基本信息")
            //    tableName = "STU_INFO";
            //else if (DL2.Text == "教师基本信息")
            //    tableName = "TEA_INFO";
            //else tableName = "COURSE_INFO";

            if (DL2.Text == "学生基本信息")
            {
                str_sql = "select * from STU_INFO";
                string column_names = "id,name,gender,collegeid,speciality,class,phone,email,state";
                DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, column_names); // Session["Field_Desc"].ToString().Trim()
            }
            else if (DL2.Text == "教师基本信息")
            {
                str_sql = "select * from TEA_INFO";
                string column_names = "id,name,gender,rank,collegeid,phone,email,state";
                DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, column_names); // Session["Field_Desc"].ToString().Trim()
            }
            else if (DL2.Text == "课程基本信息")
            {
                str_sql = "select * from COURSE_INFO";
                string column_names = "id,name,collegeid,teacherid,time,room,credit,state";
                DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, column_names); // Session["Field_Desc"].ToString().Trim()
            }
        }
        else Alert("请选择导出数据表");

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string str_sql = "";
        if (DL3.Text != "请选择删除数据表")
        {
            //if (DL2.Text == "学生基本信息")
            //    tableName = "STU_INFO";
            //else if (DL2.Text == "教师基本信息")
            //    tableName = "TEA_INFO";
            //else tableName = "COURSE_INFO";

            if (DL3.Text == "学生基本信息")
            {
               //sql = "delete from CLASS_INFO;";
               
                str_sql = "delete from STU_INFO";
                Execute(str_sql);
                //string column_names = "id,name,gender,collegeid,speciality,class,phone,email,state";
                //DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, column_names); // Session["Field_Desc"].ToString().Trim()
            }
            else if (DL3.Text == "教师基本信息")
            {
                str_sql = "delete from TEA_INFO";
                Execute(str_sql);
                //str_sql = "select * from TEA_INFO";
                //string column_names = "id,name,gender,rank,collegeid,phone,email,state";
                //DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, column_names); // Session["Field_Desc"].ToString().Trim()
            }
            else if (DL3.Text == "课程基本信息")
            {
                str_sql = "delete from COURSE_INFO";
                Execute(str_sql);
                //str_sql = "select * from COURSE_INFO";
                //string column_names = "id,name,collegeid,teacherid,time,room,credit,state";
                //DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, column_names); // Session["Field_Desc"].ToString().Trim()
            }
        }
        else Alert("请选择删除数据表");
    }

    void Execute(string sql)
    {
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();
        myConn.Close();
    }

    void Alert(string message)//用于测试，无实际意义
    {
        Response.Write("<script>alert('" + message + "')</script>");
    }
}
